﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace ath2011_sql_WPF
{
    class sql_to_code
    {
        public string sql_code;
        public string[] csharp_code;
        int type; //1=adonet 2=dataset
        public int subType; // 1=select 2=insert 3=delete 4=update
        string[] keywords;
        string[] sql_text;
        txt_to_string sqltxt;

        public sql_to_code(string sql_code, int typ)
        {
            this.sql_code = sql_code;
            this.subType = sqlType();
            this.type = typ;
        }

        public int sqlType()   //type of SQL code  = {{"SELECT"},{"INSERT"},{"DELETE"},{"UPDATE"} };
        {
            keywords = new string[4];
            keywords[0] = "SELECT";
            keywords[1] = "INSERT";
            keywords[2] = "DELETE";
            keywords[3] = "UPDATE";

            int sqlType;

            for (int i = 0; i < keywords.Length; i++)
            {
                int indexof = sql_code.IndexOf(keywords[i], StringComparison.OrdinalIgnoreCase);
                if (indexof > -1 && indexof < 7)
                {
                    return i;
                }
            }
            return -1;
        }

        void f1()
        {



        }

        public string[] convert()
        {
            if (subType != -1)
            {

                if (type == 0)  // convert type = ADO.NET
                {
                    if (keywords[subType] == "SELECT") sqltxt = new txt_to_string("adonet_select.txt");// sets a path of C# sample for converter
                    if (keywords[subType] == "INSERT") sqltxt = new txt_to_string("adonet_insert.txt");
                    if (keywords[subType] == "DELETE") sqltxt = new txt_to_string("adonet_delete.txt");
                    if (keywords[subType] == "UPDATE") sqltxt = new txt_to_string("adonet_update.txt");
                }
                if (type == 1)  // convert type = Data Set
                {
                    if (keywords[subType] == "SELECT") sqltxt = new txt_to_string("dataset_select.txt");// sets a path of C# sample for converter
                    if (keywords[subType] == "INSERT") sqltxt = new txt_to_string("dataset_insert.txt");
                    if (keywords[subType] == "DELETE") sqltxt = new txt_to_string("dataset_delete.txt");
                    if (keywords[subType] == "UPDATE") sqltxt = new txt_to_string("dataset_update.txt");
                }
                if (type == 2)  // convert type = LINQ
                {
                    if (keywords[subType] == "SELECT") sqltxt = new txt_to_string("linq_select.txt");// sets a path of C# sample for converter
                    if (keywords[subType] == "INSERT") sqltxt = new txt_to_string("linq_insert.txt");
                    if (keywords[subType] == "DELETE") sqltxt = new txt_to_string("linq_delete.txt");
                    if (keywords[subType] == "UPDATE") sqltxt = new txt_to_string("linq_update.txt");
                }
                if (type == 3)  // convert type = Stored Procedures
                {
                    if (keywords[subType] == "SELECT") sqltxt = new txt_to_string("st_select.txt");// sets a path of C# sample for converter
                    if (keywords[subType] == "INSERT") sqltxt = new txt_to_string("st_insert.txt");
                    if (keywords[subType] == "DELETE") sqltxt = new txt_to_string("st_delete.txt");
                    if (keywords[subType] == "UPDATE") sqltxt = new txt_to_string("st_update.txt");
                }
                if (type == 4)  // convert type = Stored Procedures
                {
                    if (keywords[subType] == "SELECT") sqltxt = new txt_to_string("linqSP_select.txt");// sets a path of C# sample for converter
                    if (keywords[subType] == "INSERT") sqltxt = new txt_to_string("linqSP_insert.txt");
                    if (keywords[subType] == "DELETE") sqltxt = new txt_to_string("linqSP_delete.txt");
                    if (keywords[subType] == "UPDATE") sqltxt = new txt_to_string("linqSP_update.txt");
                }
            }
            if (type > 4 || subType == -1) { sqltxt = new txt_to_string("msg_error.txt"); }
            sql_text = sqltxt.convert(); // returns string from txt

            this.csharp_code = new string[sql_text.Length];

            for (int i = 0; i < sql_text.Length; i++)
            {
                this.csharp_code[i] = sql_text[i];


                int ll = this.csharp_code[i].IndexOf("$") + 1;
                int rr = this.csharp_code[i].IndexOf("$", ll) - ll;
                int dd = 0;
                int dl = 0;
                int[] posDolars = new int[10];
                string[] orderList = new string[3];


                while ((dd = this.csharp_code[i].IndexOf("$", dd)) != -1) //source - http://www.dotnetperls.com/indexof
                {
                    posDolars[dl] = dd;
                    dd++;
                    dl++;

                    Console.WriteLine("znaleziono " + dl + " dolara" + dd + " : " + ll + " : " + rr);
                }
                string[] order = new string[dl / 2];
                string[][] temp1 = new string[dl / 2][];

                for (int b = 0; b < dl; b += 2)
                {
                    if (b + 1 <= dl)
                    {
                        Console.WriteLine(this.csharp_code[i].Substring(posDolars[b] + 1, posDolars[b + 1] - posDolars[b] - 1));
                        order[b / 2] = this.csharp_code[i].Substring(posDolars[b] + 1, posDolars[b + 1] - posDolars[b] - 1);
                    }
                }


                if (dl <= 1)  //next iteration if command not found
                { continue; }




                for (int k = 0; k < dl / 2; k++)
                {


                    string[] part = order[k].Split(';');
                    string part1 = part[0];
                    string part2 = part[1];
                    string argument = "none";
                    if (part.Length == 3) argument = part[2];

                    string[] partl = part1.Split(',');
                    string[] partr = part2.Split(',');


                    short found = 0;
                    string temp = " ";

                    int cutl = 0;
                    int cutl2 = 0;
                    int cutr = 0;
                    /*
                        For example $FROM;GROUP BY,WHERE$  
                        - search SQL command for kewords FROM and GROUP BY, 
                        if GROUP BY is missing search for WHERE, 
                        if it exist copy from SQL string fragment between FROM and WHERE,
                    */

                    for (int l = 0; l < partl.Length; l++) // finding left part of command
                    {
                        if (partl[l] == "begin") { cutl = 0; found++; break; }
                        if (sql_code.IndexOf(partl[l], StringComparison.OrdinalIgnoreCase) != -1)
                        {
                            cutl = sql_code.IndexOf(partl[l], StringComparison.OrdinalIgnoreCase) + partl[l].Length;
                            cutl2 = -sql_code.IndexOf(partl[l], StringComparison.OrdinalIgnoreCase) - partl[l].Length;
                            found++;
                            break;
                        }
                    }

                    if (found == 1)
                    {
                        for (int r = 0; r < partr.Length; r++) //finding right part only if left exist
                        {
                            if (partr[r] == "end") { cutr = sql_code.Length + cutl2; found++; break; }
                            if (sql_code.IndexOf(partr[r], StringComparison.OrdinalIgnoreCase) != -1)
                            {
                                cutr = sql_code.IndexOf(partr[r], StringComparison.OrdinalIgnoreCase) + cutl2;
                                found++;
                                break;
                            }
                        }
                    }
                    if (found == 2) // Both keywords or commands have been found
                    {
                        temp = sql_code.Substring(cutl, cutr);  //part of SQL code betwen both keywords
                        int rep = 1;
                        if (argument == "repeat")
                        {
                            temp1[k] = temp.Split(',');
                            rep = temp1.Length;
                        }
                        else if (argument != "repeat" && argument != "none")
                        {
                           //if(temp.Contains(",")==true) temp = Regex.Replace(temp, argument, "");
                            // if (temp.Contains(",") == false) temp = Regex.Replace(temp, argument, ",");
                            temp = Regex.Replace(temp, @"\s+(AND|OR).", ",", RegexOptions.IgnoreCase);   
                            temp = Regex.Replace(temp, argument, "");
                            temp1[k] = temp.Split(',');
                            rep = temp1.Length;
                        }
                        else
                        {
                            temp1[k] = new string[1];
                            temp1[k][0] = temp;
                            rep = 1;
                        }
                    }
                    if (found == 0) { temp1[k] = new string[1]; temp1[k][0] = "";}
                }
                

                string tempc = this.csharp_code[i];
                this.csharp_code[i] = "";
                
                    for (int h = 0; h < temp1[0].Length; h++)
                    {
                        //   temp1[k][h] = Regex.Replace(temp1[k][h], @"^\s+|\s+$", ""); 

                        string t1 = tempc;
                        for (int k = 0; k < temp1.Length; k++)
                        {
                            temp1[k][h] = Regex.Replace(temp1[k][h], @"^\s+|\s+$", "");
                            t1 = t1.Replace("$" + order[k] + "$", temp1[k][h]);

                        }
                        this.csharp_code[i] += t1 + "\r\n";
                    }
                }

            
            return this.csharp_code;
        } 
    }
}
